package com.tuanzi.loan.business.repository.family.impl;

import com.tuanzi.loan.business.repository.family.LoanLargeAmountVipBindRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepository;
import com.tuanzi.loan.vo.request.family.largeamount.LoanLargeAmountBindRequest;
import com.tuanzi.loan.vo.response.family.largeamount.LoanLargeAmountBindResponse;
import com.tuanzi.server.loan_base.util.DateUtils;
import org.apache.commons.lang3.StringUtils;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

import static com.tuanzi.loan.generated.jooq.loan.Tables.LOAN_LARGE_AMOUNT_VIP_BINDING_INFO;

/**
 * User: liangZongXi
 * Create time: 11:54 2019/5/22
 * Description:
 */

@Repository
public class LoanLargeAmountVipBindRepositoryImpl extends JOOQBaseRepository implements LoanLargeAmountVipBindRepository {
    @Override
    public Page<LoanLargeAmountBindResponse> list(LoanLargeAmountBindRequest searchRequest, Pageable pageable) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT bind.id,bind.card_grade,bind.card_name,bind.card_num,bind.binding_phone,bind.btime,bind.due_time,bind.emp_num,apply.loan_count,zx.total,zx.total_percon")
            .append(" FROM loan_large_amount_vip_binding_info bind LEFT JOIN loan_large_amount_gongzhong_user_apply apply ON bind.binding_phone = apply.mob_num")
                .append(" LEFT JOIN ( SELECT b.card_num, b.total,e.total_percon FROM ( SELECT a.card_num,COUNT(a.card_num) AS total FROM loan_wholesale_credit_record a GROUP BY a.card_num ) b")
                .append(" LEFT OUTER JOIN ( SELECT d.card_num, COUNT(d.card_num) AS total_percon FROM (")
                .append(" SELECT c.card_num FROM loan_wholesale_credit_record c GROUP BY c.card_num, c.mobnum,c.user_name,c.idcard ) d")
                .append(" GROUP BY d.card_num ) e ON b.card_num = e.card_num ) zx ON zx.card_num = bind.card_num")
                .append(" WHERE bind.btime IS NOT NULL");

        //添加条件
        Integer cardGrade = searchRequest.getCardGrade();
        if(null != cardGrade){
            sql.append(" and bind.card_grade = ").append(cardGrade);
        }
        String cardNum = searchRequest.getCardNum();
        if(StringUtils.isNotBlank(cardNum)){
            sql.append(" and bind.card_num ='").append(cardNum).append("'");
        }
        Date startDate = searchRequest.getStartDate();
        if(null != startDate){
            String s = DateUtils.formatDateToYyyyMMdd(startDate);
            String date = s + " 00:00:00";
            sql.append(" and bind.btime >='").append(date).append("'");
        }
        Date endDate = searchRequest.getEndDate();
        if(null != endDate){
            String s = DateUtils.formatDateToYyyyMMdd(endDate);
            String date = s + " 23:59:59";
            sql.append(" and bind.btime <='").append(date).append("'");
        }
        String phone = searchRequest.getPhone();
        if(StringUtils.isNotBlank(phone)){
            sql.append(" and bind.binding_phone ='").append(phone).append("'");
        }
        String name = searchRequest.getName();
        if(StringUtils.isNotBlank(name)){
            sql.append(" and bind.card_name ='").append(name).append("'");
        }
        String empNum = searchRequest.getEmpNum();
        if(StringUtils.isNotBlank(empNum)){
            sql.append(" and bind.emp_num ='").append(empNum).append("'");
        }

        sql.append(" ORDER BY bind.btime desc");

        //查询数量sql
        String countSql = "SELECT COUNT(*) AS num FROM (" + sql.toString() + ") countd";

        Long count = dslContext.fetchOne(countSql).into(Long.class);
        String querySql = sql + " LIMIT ?,?";
        Object[] params= {pageable.getOffset(), pageable.getPageSize()};

        List<LoanLargeAmountBindResponse> list = dslContext.fetch(querySql, params).map(record -> {
            LoanLargeAmountBindResponse response = new LoanLargeAmountBindResponse();
            response.setId(record.get("id", Long.class));
            response.setName(record.get("card_name", String.class));
            response.setPhone(record.get("binding_phone", String.class));
            response.setCardNum(record.get("card_num", String.class));
            response.setCardGrade(record.get("card_grade", Integer.class));
            response.setActivateDate(record.get("btime", Date.class));
            response.setDueDate(record.get("due_time", Date.class));
            response.setSecondLoan(record.get("loan_count", String.class));
            response.setEmpNum(record.get("emp_num", String.class));
            response.setZxCount(record.get("total", String.class));
            response.setZxUserCount(record.get("total_percon", String.class));

            return response;
        });

        return new PageImpl<>(list, pageable, count);
    }



    @Override
    protected TableImpl getTable() {
        return LOAN_LARGE_AMOUNT_VIP_BINDING_INFO;
    }
}
